Scalar Subqueries

Some databases allow entire SQL statements (called a subquery) to be used as a field in the SELECT clause of  another query. As each row is processed in the “main” query, the subquery is called to fill in the value for that particular field.  These subqueries must return a single value (or no value) for each row in the main query.  In the SQL below (using the sample database), a scalar subquery (highlighted) is being used to return the latest PO date for an individual employee (if it exists).  In other words, the subquery creates a select statement that returns the latest PO.

SELECT Employees.emp_id, last_name, first_name,
(SELECT MAX (po_date)from Purchase_Orders WHERE Employees.emp_id = Purchase_Orders.employee_id) AS latest
FROM Employees
WHERE Employees.emp_id = ’501’

Note the use of the MAX function which will return one value.

This technique can be used for retrieving a data element from a table if you don’t wish to include that table in the main query.  This can be a useful way to avoid the use of outer joins, which can negatively impact query performance.

The technique for creating the query/subquery to find the latest Purchase Order created by Employee_ID = 501 follows.

Creating the Main Query

Begin by entering emp_id, last_name, and first_name into the SELECT clause. Enter emp_id into the WHERE clause and set the condition to ='501'.

Creating the Subquery

On the SELECT tab, create a calculated field by creating a new column, click within the “Field” row, then click the ellipsis to bring up the SQL Editor.  Enter the subquery as shown below. Remember to enclose the subquery with parentheses.

Give the calculated field an alias as shown below.

Results

Save your changes and execute the query. Executing the query displays the latest PO date for employee 501.  There are 19 purchase orders created by this employee, with 11/19/2008 being the latest.